{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# More JOIN operations\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/1/10/Movie-er.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 1962 movies\n",
    "\n",
    "List the films where the **yr** is 1962 [Show **id, title**]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "movie = pd.read_sql_table('movie', engine)\n",
    "actor = pd.read_sql_table('actor', engine)\n",
    "casting = pd.read_sql_table('casting', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>10212</td>\n",
       "      <td>A Kind of Loving</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>10329</td>\n",
       "      <td>A Symposium on Popular Songs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>10347</td>\n",
       "      <td>A Very Private Affair (Vie PrivÃ©e)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>10648</td>\n",
       "      <td>An Autumn Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>867</th>\n",
       "      <td>10868</td>\n",
       "      <td>Atraco a las tres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1005</th>\n",
       "      <td>11006</td>\n",
       "      <td>Barabbas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1052</th>\n",
       "      <td>11053</td>\n",
       "      <td>Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1198</th>\n",
       "      <td>11199</td>\n",
       "      <td>Big and Little Wong Tin Bar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1229</th>\n",
       "      <td>11230</td>\n",
       "      <td>Billy Budd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1233</th>\n",
       "      <td>11234</td>\n",
       "      <td>Billy Rose's Jumbo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1241</th>\n",
       "      <td>11242</td>\n",
       "      <td>Birdman of Alcatraz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1372</th>\n",
       "      <td>11373</td>\n",
       "      <td>Boccaccio '70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1390</th>\n",
       "      <td>11391</td>\n",
       "      <td>Bon Voyage!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1438</th>\n",
       "      <td>11439</td>\n",
       "      <td>Boys' Night Out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1691</th>\n",
       "      <td>11692</td>\n",
       "      <td>Cape Fear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1734</th>\n",
       "      <td>11735</td>\n",
       "      <td>Carnival of Souls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1752</th>\n",
       "      <td>11753</td>\n",
       "      <td>Carry On Cruising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2367</th>\n",
       "      <td>12368</td>\n",
       "      <td>David and Lisa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2383</th>\n",
       "      <td>12384</td>\n",
       "      <td>Days of Wine and Roses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2709</th>\n",
       "      <td>12710</td>\n",
       "      <td>Dr. No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2816</th>\n",
       "      <td>12817</td>\n",
       "      <td>L'Eclisse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2966</th>\n",
       "      <td>12967</td>\n",
       "      <td>Tutti a casa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2991</th>\n",
       "      <td>12992</td>\n",
       "      <td>Experiment in Terror</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3009</th>\n",
       "      <td>13010</td>\n",
       "      <td>Eyes Without a Face</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3483</th>\n",
       "      <td>13484</td>\n",
       "      <td>Gay Purr-ee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3533</th>\n",
       "      <td>13534</td>\n",
       "      <td>Gigot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3640</th>\n",
       "      <td>13641</td>\n",
       "      <td>Gorath</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3726</th>\n",
       "      <td>13727</td>\n",
       "      <td>Gypsy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3740</th>\n",
       "      <td>13741</td>\n",
       "      <td>Half Ticket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3797</th>\n",
       "      <td>13798</td>\n",
       "      <td>Harakiri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4316</th>\n",
       "      <td>14317</td>\n",
       "      <td>In Search of the Castaways</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4453</th>\n",
       "      <td>14454</td>\n",
       "      <td>It's Only Money</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4549</th>\n",
       "      <td>14550</td>\n",
       "      <td>Jigsaw</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4717</th>\n",
       "      <td>14718</td>\n",
       "      <td>Kid Galahad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4859</th>\n",
       "      <td>14860</td>\n",
       "      <td>La commare secca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4872</th>\n",
       "      <td>14873</td>\n",
       "      <td>La notte</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4971</th>\n",
       "      <td>14972</td>\n",
       "      <td>Lawrence of Arabia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5087</th>\n",
       "      <td>15088</td>\n",
       "      <td>Life for Ruth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5172</th>\n",
       "      <td>15173</td>\n",
       "      <td>Lolita</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5181</th>\n",
       "      <td>15182</td>\n",
       "      <td>Long Day's Journey into Night</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5246</th>\n",
       "      <td>15247</td>\n",
       "      <td>Love at Twenty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5296</th>\n",
       "      <td>15297</td>\n",
       "      <td>Lycanthropus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5396</th>\n",
       "      <td>15397</td>\n",
       "      <td>Mamma Roma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5563</th>\n",
       "      <td>15564</td>\n",
       "      <td>Merrill's Marauders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5751</th>\n",
       "      <td>15752</td>\n",
       "      <td>Mother Joan of the Angels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5778</th>\n",
       "      <td>15779</td>\n",
       "      <td>Mr. Hobbs Takes a Vacation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5839</th>\n",
       "      <td>15840</td>\n",
       "      <td>Mutiny on the Bounty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6202</th>\n",
       "      <td>16203</td>\n",
       "      <td>On the Beat</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6294</th>\n",
       "      <td>16295</td>\n",
       "      <td>Os Cafajestes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6366</th>\n",
       "      <td>16367</td>\n",
       "      <td>Panic in Year Zero!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6461</th>\n",
       "      <td>16462</td>\n",
       "      <td>Period of Adjustment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6484</th>\n",
       "      <td>16485</td>\n",
       "      <td>Phaedra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6532</th>\n",
       "      <td>16533</td>\n",
       "      <td>Pitfall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6621</th>\n",
       "      <td>16622</td>\n",
       "      <td>Pressure Point</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6660</th>\n",
       "      <td>16661</td>\n",
       "      <td>Prison</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6674</th>\n",
       "      <td>16675</td>\n",
       "      <td>Professor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6944</th>\n",
       "      <td>16945</td>\n",
       "      <td>Ride the High Country</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7147</th>\n",
       "      <td>17148</td>\n",
       "      <td>Salvatore Giuliano</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7295</th>\n",
       "      <td>17296</td>\n",
       "      <td>Sergeants 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7803</th>\n",
       "      <td>17804</td>\n",
       "      <td>State Fair</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7943</th>\n",
       "      <td>17944</td>\n",
       "      <td>Sundays and Cybele</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8000</th>\n",
       "      <td>18001</td>\n",
       "      <td>Sweet Bird of Youth (film)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8126</th>\n",
       "      <td>18127</td>\n",
       "      <td>Term of Trial</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8170</th>\n",
       "      <td>18171</td>\n",
       "      <td>That Touch of Mink</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8176</th>\n",
       "      <td>18177</td>\n",
       "      <td>The 300 Spartans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8729</th>\n",
       "      <td>18730</td>\n",
       "      <td>The Counterfeit Traitor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8940</th>\n",
       "      <td>18941</td>\n",
       "      <td>The Exterminating Angel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9033</th>\n",
       "      <td>19034</td>\n",
       "      <td>The Four Days of Naples</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9429</th>\n",
       "      <td>19430</td>\n",
       "      <td>The Intruder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9519</th>\n",
       "      <td>19520</td>\n",
       "      <td>The L-Shaped Room</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9678</th>\n",
       "      <td>19679</td>\n",
       "      <td>The Loneliness of the Long Distance Runner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9693</th>\n",
       "      <td>19694</td>\n",
       "      <td>The Longest Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9794</th>\n",
       "      <td>19795</td>\n",
       "      <td>The Man Who Shot Liberty Valance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9807</th>\n",
       "      <td>19808</td>\n",
       "      <td>The Manchurian Candidate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9932</th>\n",
       "      <td>19933</td>\n",
       "      <td>The Music Man</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10113</th>\n",
       "      <td>20114</td>\n",
       "      <td>The Phantom of the Opera</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10160</th>\n",
       "      <td>20161</td>\n",
       "      <td>The Premature Burial</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10479</th>\n",
       "      <td>20730</td>\n",
       "      <td>The Trial</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10800</th>\n",
       "      <td>21051</td>\n",
       "      <td>Through a Glass Darkly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10859</th>\n",
       "      <td>21110</td>\n",
       "      <td>To Kill a Mockingbird</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10938</th>\n",
       "      <td>21189</td>\n",
       "      <td>Tower of London</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11073</th>\n",
       "      <td>21324</td>\n",
       "      <td>Two Half Times in Hell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11211</th>\n",
       "      <td>21462</td>\n",
       "      <td>Varan the Unbelievable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11243</th>\n",
       "      <td>21494</td>\n",
       "      <td>Village of Daughters</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11422</th>\n",
       "      <td>21673</td>\n",
       "      <td>What Ever Happened to Baby Jane?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11510</th>\n",
       "      <td>21761</td>\n",
       "      <td>Who's Got the Action?</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id                                        title\n",
       "211    10212                             A Kind of Loving\n",
       "328    10329                 A Symposium on Popular Songs\n",
       "346    10347          A Very Private Affair (Vie PrivÃ©e)\n",
       "647    10648                          An Autumn Afternoon\n",
       "867    10868                            Atraco a las tres\n",
       "1005   11006                                     Barabbas\n",
       "1052   11053  Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)\n",
       "1198   11199                  Big and Little Wong Tin Bar\n",
       "1229   11230                                   Billy Budd\n",
       "1233   11234                           Billy Rose's Jumbo\n",
       "1241   11242                          Birdman of Alcatraz\n",
       "1372   11373                                Boccaccio '70\n",
       "1390   11391                                  Bon Voyage!\n",
       "1438   11439                              Boys' Night Out\n",
       "1691   11692                                    Cape Fear\n",
       "1734   11735                            Carnival of Souls\n",
       "1752   11753                            Carry On Cruising\n",
       "2367   12368                               David and Lisa\n",
       "2383   12384                       Days of Wine and Roses\n",
       "2709   12710                                       Dr. No\n",
       "2816   12817                                    L'Eclisse\n",
       "2966   12967                                 Tutti a casa\n",
       "2991   12992                         Experiment in Terror\n",
       "3009   13010                          Eyes Without a Face\n",
       "3483   13484                                  Gay Purr-ee\n",
       "3533   13534                                        Gigot\n",
       "3640   13641                                       Gorath\n",
       "3726   13727                                        Gypsy\n",
       "3740   13741                                  Half Ticket\n",
       "3797   13798                                     Harakiri\n",
       "4316   14317                   In Search of the Castaways\n",
       "4453   14454                              It's Only Money\n",
       "4549   14550                                       Jigsaw\n",
       "4717   14718                                  Kid Galahad\n",
       "4859   14860                             La commare secca\n",
       "4872   14873                                     La notte\n",
       "4971   14972                           Lawrence of Arabia\n",
       "5087   15088                                Life for Ruth\n",
       "5172   15173                                       Lolita\n",
       "5181   15182                Long Day's Journey into Night\n",
       "5246   15247                               Love at Twenty\n",
       "5296   15297                                 Lycanthropus\n",
       "5396   15397                                   Mamma Roma\n",
       "5563   15564                          Merrill's Marauders\n",
       "5751   15752                    Mother Joan of the Angels\n",
       "5778   15779                   Mr. Hobbs Takes a Vacation\n",
       "5839   15840                         Mutiny on the Bounty\n",
       "6202   16203                                  On the Beat\n",
       "6294   16295                                Os Cafajestes\n",
       "6366   16367                          Panic in Year Zero!\n",
       "6461   16462                         Period of Adjustment\n",
       "6484   16485                                      Phaedra\n",
       "6532   16533                                      Pitfall\n",
       "6621   16622                               Pressure Point\n",
       "6660   16661                                       Prison\n",
       "6674   16675                                    Professor\n",
       "6944   16945                        Ride the High Country\n",
       "7147   17148                           Salvatore Giuliano\n",
       "7295   17296                                  Sergeants 3\n",
       "7803   17804                                   State Fair\n",
       "7943   17944                           Sundays and Cybele\n",
       "8000   18001                   Sweet Bird of Youth (film)\n",
       "8126   18127                                Term of Trial\n",
       "8170   18171                           That Touch of Mink\n",
       "8176   18177                             The 300 Spartans\n",
       "8729   18730                      The Counterfeit Traitor\n",
       "8940   18941                      The Exterminating Angel\n",
       "9033   19034                      The Four Days of Naples\n",
       "9429   19430                                 The Intruder\n",
       "9519   19520                            The L-Shaped Room\n",
       "9678   19679   The Loneliness of the Long Distance Runner\n",
       "9693   19694                              The Longest Day\n",
       "9794   19795             The Man Who Shot Liberty Valance\n",
       "9807   19808                     The Manchurian Candidate\n",
       "9932   19933                                The Music Man\n",
       "10113  20114                     The Phantom of the Opera\n",
       "10160  20161                         The Premature Burial\n",
       "10479  20730                                    The Trial\n",
       "10800  21051                       Through a Glass Darkly\n",
       "10859  21110                        To Kill a Mockingbird\n",
       "10938  21189                              Tower of London\n",
       "11073  21324                       Two Half Times in Hell\n",
       "11211  21462                       Varan the Unbelievable\n",
       "11243  21494                         Village of Daughters\n",
       "11422  21673             What Ever Happened to Baby Jane?\n",
       "11510  21761                        Who's Got the Action?"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie.loc[movie['yr']==1962, ['id', 'title']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. When was Citizen Kane released?\n",
    "\n",
    "Give year of 'Citizen Kane'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1954</th>\n",
       "      <td>1941</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        yr\n",
       "1954  1941"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie.loc[movie['title']=='Citizen Kane', ['yr']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Star Trek movies\n",
    "\n",
    "List all of the Star Trek movies, include the **id**, **title** and **yr** (all of these movies include the words Star Trek in the title). Order results by year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>yr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7769</th>\n",
       "      <td>17770</td>\n",
       "      <td>Star Trek: First Contact</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7770</th>\n",
       "      <td>17771</td>\n",
       "      <td>Star Trek: Insurrection</td>\n",
       "      <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7771</th>\n",
       "      <td>17772</td>\n",
       "      <td>Star Trek: The Motion Picture</td>\n",
       "      <td>1979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7772</th>\n",
       "      <td>17773</td>\n",
       "      <td>Star Trek</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7773</th>\n",
       "      <td>17774</td>\n",
       "      <td>Star Trek Generations</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7774</th>\n",
       "      <td>17775</td>\n",
       "      <td>Star Trek II: The Wrath of Khan</td>\n",
       "      <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7775</th>\n",
       "      <td>17776</td>\n",
       "      <td>Star Trek III: The Search for Spock</td>\n",
       "      <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7776</th>\n",
       "      <td>17777</td>\n",
       "      <td>Star Trek IV: The Voyage Home</td>\n",
       "      <td>1986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7777</th>\n",
       "      <td>17778</td>\n",
       "      <td>Star Trek Nemesis</td>\n",
       "      <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7778</th>\n",
       "      <td>17779</td>\n",
       "      <td>Star Trek V: The Final Frontier</td>\n",
       "      <td>1989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7779</th>\n",
       "      <td>17780</td>\n",
       "      <td>Star Trek VI: The Undiscovered Country</td>\n",
       "      <td>1991</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                                   title    yr\n",
       "7769  17770                Star Trek: First Contact  1996\n",
       "7770  17771                 Star Trek: Insurrection  1998\n",
       "7771  17772           Star Trek: The Motion Picture  1979\n",
       "7772  17773                               Star Trek  2009\n",
       "7773  17774                   Star Trek Generations  1994\n",
       "7774  17775         Star Trek II: The Wrath of Khan  1982\n",
       "7775  17776     Star Trek III: The Search for Spock  1984\n",
       "7776  17777           Star Trek IV: The Voyage Home  1986\n",
       "7777  17778                       Star Trek Nemesis  2002\n",
       "7778  17779         Star Trek V: The Final Frontier  1989\n",
       "7779  17780  Star Trek VI: The Undiscovered Country  1991"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie.loc[movie['title'].str.lower().str.contains('star trek').fillna(False),\n",
    "         ['id', 'title', 'yr']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. id for actor Glenn Close\n",
    "\n",
    "What **id** number does the actor 'Glenn Close' have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22511</th>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id\n",
       "22511  140"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "actor.loc[actor['name']=='Glenn Close', ['id']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. id for Casablanca\n",
    "\n",
    "What is the **id** of the film 'Casablanca'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1767</th>\n",
       "      <td>11768</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id\n",
       "1767  11768"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie.loc[movie['title']=='Casablanca', ['id']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Cast list for Casablanca\n",
    "\n",
    "Obtain the cast list for 'Casablanca'.\n",
    "\n",
    "> _what is a cast list?_  \n",
    "> The cast list is the names of the actors who were in the movie.\n",
    "\n",
    "Use **movieid=11768**, (or whatever value you got from the previous question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3766</th>\n",
       "      <td>Peter Lorre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8665</th>\n",
       "      <td>John Qualen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8938</th>\n",
       "      <td>Madeleine LeBeau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15264</th>\n",
       "      <td>Jack Benny</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24061</th>\n",
       "      <td>Norma Varden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24380</th>\n",
       "      <td>Ingrid Bergman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24740</th>\n",
       "      <td>Conrad Veidt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25362</th>\n",
       "      <td>Leon Belasco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27263</th>\n",
       "      <td>Humphrey Bogart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27294</th>\n",
       "      <td>Sydney Greenstreet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33018</th>\n",
       "      <td>Leonid Kinskey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36796</th>\n",
       "      <td>Wolfgang Zilzer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41081</th>\n",
       "      <td>Claude Rains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44525</th>\n",
       "      <td>Curt Bois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53863</th>\n",
       "      <td>Leo White</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64771</th>\n",
       "      <td>Ludwig StÃ¶ssel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65164</th>\n",
       "      <td>Marcel Dalio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66856</th>\n",
       "      <td>Dan Seymour</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66861</th>\n",
       "      <td>Paul Henreid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66873</th>\n",
       "      <td>Joy Page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66875</th>\n",
       "      <td>S. Z. Sakall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66879</th>\n",
       "      <td>Dooley Wilson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66882</th>\n",
       "      <td>William Edmunds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66889</th>\n",
       "      <td>Gregory Gaye</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66898</th>\n",
       "      <td>Torben Meyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66908</th>\n",
       "      <td>Georges Renavent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66914</th>\n",
       "      <td>Jean Del Val</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66917</th>\n",
       "      <td>Louis V. Arco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66918</th>\n",
       "      <td>Trude Berliner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66919</th>\n",
       "      <td>Ilka GrÃ¼nig</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66920</th>\n",
       "      <td>Richard Ryen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66921</th>\n",
       "      <td>Hans Twardowski</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name\n",
       "3766          Peter Lorre\n",
       "8665          John Qualen\n",
       "8938     Madeleine LeBeau\n",
       "15264          Jack Benny\n",
       "24061        Norma Varden\n",
       "24380      Ingrid Bergman\n",
       "24740        Conrad Veidt\n",
       "25362        Leon Belasco\n",
       "27263     Humphrey Bogart\n",
       "27294  Sydney Greenstreet\n",
       "33018      Leonid Kinskey\n",
       "36796     Wolfgang Zilzer\n",
       "41081        Claude Rains\n",
       "44525           Curt Bois\n",
       "53863           Leo White\n",
       "64771     Ludwig StÃ¶ssel\n",
       "65164        Marcel Dalio\n",
       "66856         Dan Seymour\n",
       "66861        Paul Henreid\n",
       "66873            Joy Page\n",
       "66875        S. Z. Sakall\n",
       "66879       Dooley Wilson\n",
       "66882     William Edmunds\n",
       "66889        Gregory Gaye\n",
       "66898        Torben Meyer\n",
       "66908    Georges Renavent\n",
       "66914        Jean Del Val\n",
       "66917       Louis V. Arco\n",
       "66918      Trude Berliner\n",
       "66919        Ilka GrÃ¼nig\n",
       "66920        Richard Ryen\n",
       "66921     Hans Twardowski"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = casting.merge(actor, how='inner', left_on='actorid', right_on='id')\n",
    "a.loc[a['movieid']==11768, ['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Alien cast list\n",
    "\n",
    "Obtain the cast list for the film 'Alien'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5425</th>\n",
       "      <td>John Hurt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5426</th>\n",
       "      <td>Sigourney Weaver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5427</th>\n",
       "      <td>Yaphet Kotto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5428</th>\n",
       "      <td>Harry Dean Stanton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5429</th>\n",
       "      <td>Ian Holm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5430</th>\n",
       "      <td>Tom Skerritt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5431</th>\n",
       "      <td>Veronica Cartwright</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name\n",
       "5425            John Hurt\n",
       "5426     Sigourney Weaver\n",
       "5427         Yaphet Kotto\n",
       "5428   Harry Dean Stanton\n",
       "5429             Ian Holm\n",
       "5430         Tom Skerritt\n",
       "5431  Veronica Cartwright"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (movie.merge(casting, how='right', left_on='id', right_on='movieid')\n",
    "     .merge(actor, how='left', left_on='actorid', right_on='id'))\n",
    "a.loc[a['title']=='Alien', ['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Harrison Ford movies\n",
    "\n",
    "List the films in which 'Harrison Ford' has appeared"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15429</th>\n",
       "      <td>A Hundred and One Nights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15430</th>\n",
       "      <td>Air Force One</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15431</th>\n",
       "      <td>American Graffiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15432</th>\n",
       "      <td>Apocalypse Now</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15433</th>\n",
       "      <td>Clear and Present Danger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15434</th>\n",
       "      <td>Cowboys &amp; Aliens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15435</th>\n",
       "      <td>Crossing Over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15436</th>\n",
       "      <td>Dead Heat on a Merry-Go-Round</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15437</th>\n",
       "      <td>Extraordinary Measures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15438</th>\n",
       "      <td>Firewall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15439</th>\n",
       "      <td>Force 10 From Navarone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15440</th>\n",
       "      <td>Hanover Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15441</th>\n",
       "      <td>Hawthorne of the U.S.A.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15442</th>\n",
       "      <td>Hollywood Homicide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15443</th>\n",
       "      <td>Indiana Jones and the Kingdom of the Crystal S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15444</th>\n",
       "      <td>Indiana Jones and the Last Crusade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15445</th>\n",
       "      <td>Indiana Jones and the Temple of Doom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15446</th>\n",
       "      <td>Jimmy Hollywood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15447</th>\n",
       "      <td>K-19: The Widowmaker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15448</th>\n",
       "      <td>More American Graffiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15449</th>\n",
       "      <td>Morning Glory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15450</th>\n",
       "      <td>Patriot Games</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15451</th>\n",
       "      <td>Presumed Innocent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15452</th>\n",
       "      <td>Raiders of the Lost Ark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15453</th>\n",
       "      <td>Random Hearts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15454</th>\n",
       "      <td>Regarding Henry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15455</th>\n",
       "      <td>Sabrina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15456</th>\n",
       "      <td>Sally of the Sawdust</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15457</th>\n",
       "      <td>Shadows</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15458</th>\n",
       "      <td>Six Days Seven Nights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15459</th>\n",
       "      <td>Smilin' Through</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15460</th>\n",
       "      <td>Star Wars Episode IV: A New Hope</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15461</th>\n",
       "      <td>Star Wars Episode V: The Empire Strikes Back</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15462</th>\n",
       "      <td>Star Wars Episode VI: Return of the Jedi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15463</th>\n",
       "      <td>The Conversation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15464</th>\n",
       "      <td>The Devil's Own</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15465</th>\n",
       "      <td>The Fugitive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15466</th>\n",
       "      <td>The Mosquito Coast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15467</th>\n",
       "      <td>What Lies Beneath</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15468</th>\n",
       "      <td>Witness</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15469</th>\n",
       "      <td>Working Girl</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   title\n",
       "15429                           A Hundred and One Nights\n",
       "15430                                      Air Force One\n",
       "15431                                  American Graffiti\n",
       "15432                                     Apocalypse Now\n",
       "15433                           Clear and Present Danger\n",
       "15434                                   Cowboys & Aliens\n",
       "15435                                      Crossing Over\n",
       "15436                      Dead Heat on a Merry-Go-Round\n",
       "15437                             Extraordinary Measures\n",
       "15438                                           Firewall\n",
       "15439                             Force 10 From Navarone\n",
       "15440                                     Hanover Street\n",
       "15441                            Hawthorne of the U.S.A.\n",
       "15442                                 Hollywood Homicide\n",
       "15443  Indiana Jones and the Kingdom of the Crystal S...\n",
       "15444                 Indiana Jones and the Last Crusade\n",
       "15445               Indiana Jones and the Temple of Doom\n",
       "15446                                    Jimmy Hollywood\n",
       "15447                               K-19: The Widowmaker\n",
       "15448                             More American Graffiti\n",
       "15449                                      Morning Glory\n",
       "15450                                      Patriot Games\n",
       "15451                                  Presumed Innocent\n",
       "15452                            Raiders of the Lost Ark\n",
       "15453                                      Random Hearts\n",
       "15454                                    Regarding Henry\n",
       "15455                                            Sabrina\n",
       "15456                               Sally of the Sawdust\n",
       "15457                                            Shadows\n",
       "15458                              Six Days Seven Nights\n",
       "15459                                    Smilin' Through\n",
       "15460                   Star Wars Episode IV: A New Hope\n",
       "15461       Star Wars Episode V: The Empire Strikes Back\n",
       "15462           Star Wars Episode VI: Return of the Jedi\n",
       "15463                                   The Conversation\n",
       "15464                                    The Devil's Own\n",
       "15465                                       The Fugitive\n",
       "15466                                 The Mosquito Coast\n",
       "15467                                  What Lies Beneath\n",
       "15468                                            Witness\n",
       "15469                                       Working Girl"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #7\n",
    "a.loc[a['name']=='Harrison Ford', ['title']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Harrison Ford as a supporting actor\n",
    "\n",
    "List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15429</th>\n",
       "      <td>A Hundred and One Nights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15431</th>\n",
       "      <td>American Graffiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15432</th>\n",
       "      <td>Apocalypse Now</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15434</th>\n",
       "      <td>Cowboys &amp; Aliens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15436</th>\n",
       "      <td>Dead Heat on a Merry-Go-Round</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15437</th>\n",
       "      <td>Extraordinary Measures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15439</th>\n",
       "      <td>Force 10 From Navarone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15441</th>\n",
       "      <td>Hawthorne of the U.S.A.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15446</th>\n",
       "      <td>Jimmy Hollywood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15448</th>\n",
       "      <td>More American Graffiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15449</th>\n",
       "      <td>Morning Glory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15456</th>\n",
       "      <td>Sally of the Sawdust</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15457</th>\n",
       "      <td>Shadows</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15459</th>\n",
       "      <td>Smilin' Through</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15460</th>\n",
       "      <td>Star Wars Episode IV: A New Hope</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15461</th>\n",
       "      <td>Star Wars Episode V: The Empire Strikes Back</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15462</th>\n",
       "      <td>Star Wars Episode VI: Return of the Jedi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15463</th>\n",
       "      <td>The Conversation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15469</th>\n",
       "      <td>Working Girl</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              title\n",
       "15429                      A Hundred and One Nights\n",
       "15431                             American Graffiti\n",
       "15432                                Apocalypse Now\n",
       "15434                              Cowboys & Aliens\n",
       "15436                 Dead Heat on a Merry-Go-Round\n",
       "15437                        Extraordinary Measures\n",
       "15439                        Force 10 From Navarone\n",
       "15441                       Hawthorne of the U.S.A.\n",
       "15446                               Jimmy Hollywood\n",
       "15448                        More American Graffiti\n",
       "15449                                 Morning Glory\n",
       "15456                          Sally of the Sawdust\n",
       "15457                                       Shadows\n",
       "15459                               Smilin' Through\n",
       "15460              Star Wars Episode IV: A New Hope\n",
       "15461  Star Wars Episode V: The Empire Strikes Back\n",
       "15462      Star Wars Episode VI: Return of the Jedi\n",
       "15463                              The Conversation\n",
       "15469                                  Working Girl"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #7\n",
    "a.loc[(a['name']=='Harrison Ford') & (a['ord']>1), ['title']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Lead actors in 1962 movies\n",
    "\n",
    "List the films together with the leading star for all 1962 films."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3454</th>\n",
       "      <td>Birdman of Alcatraz</td>\n",
       "      <td>Burt Lancaster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4041</th>\n",
       "      <td>What Ever Happened to Baby Jane?</td>\n",
       "      <td>Bette Davis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4099</th>\n",
       "      <td>David and Lisa</td>\n",
       "      <td>Keir Dullea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5668</th>\n",
       "      <td>Experiment in Terror</td>\n",
       "      <td>Glenn Ford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6323</th>\n",
       "      <td>Who's Got the Action?</td>\n",
       "      <td>Dean Martin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6334</th>\n",
       "      <td>It's Only Money</td>\n",
       "      <td>Jerry Lewis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7302</th>\n",
       "      <td>Term of Trial</td>\n",
       "      <td>Laurence Olivier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8093</th>\n",
       "      <td>Boys' Night Out</td>\n",
       "      <td>Kim Novak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8696</th>\n",
       "      <td>La notte</td>\n",
       "      <td>Marcello Mastroianni</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10637</th>\n",
       "      <td>Long Day's Journey into Night</td>\n",
       "      <td>Katharine Hepburn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11199</th>\n",
       "      <td>Gay Purr-ee</td>\n",
       "      <td>Judy Garland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12688</th>\n",
       "      <td>Mutiny on the Bounty</td>\n",
       "      <td>Marlon Brando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13107</th>\n",
       "      <td>A Kind of Loving</td>\n",
       "      <td>Alan Bates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13173</th>\n",
       "      <td>Tutti a casa</td>\n",
       "      <td>Alberto Sordi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13668</th>\n",
       "      <td>Period of Adjustment</td>\n",
       "      <td>Anthony Franciosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15216</th>\n",
       "      <td>An Autumn Afternoon</td>\n",
       "      <td>Chishu Ryu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15244</th>\n",
       "      <td>Sergeants 3</td>\n",
       "      <td>Frank Sinatra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15254</th>\n",
       "      <td>The Manchurian Candidate</td>\n",
       "      <td>Frank Sinatra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15421</th>\n",
       "      <td>L'Eclisse</td>\n",
       "      <td>Alain Delon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15674</th>\n",
       "      <td>Love at Twenty</td>\n",
       "      <td>Jean-Pierre LÃ©aud</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16583</th>\n",
       "      <td>Panic in Year Zero!</td>\n",
       "      <td>Ray Milland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16594</th>\n",
       "      <td>The Premature Burial</td>\n",
       "      <td>Ray Milland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18690</th>\n",
       "      <td>Sweet Bird of Youth (film)</td>\n",
       "      <td>Paul Newman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19133</th>\n",
       "      <td>Dr. No</td>\n",
       "      <td>Sean Connery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19511</th>\n",
       "      <td>Pressure Point</td>\n",
       "      <td>Sidney Poitier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20641</th>\n",
       "      <td>Tower of London</td>\n",
       "      <td>Vincent Price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20958</th>\n",
       "      <td>The Phantom of the Opera</td>\n",
       "      <td>Herbert Lom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22120</th>\n",
       "      <td>A Symposium on Popular Songs</td>\n",
       "      <td>Paul Frees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23204</th>\n",
       "      <td>In Search of the Castaways</td>\n",
       "      <td>Hayley Mills</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23392</th>\n",
       "      <td>A Very Private Affair (Vie PrivÃ©e)</td>\n",
       "      <td>Brigitte Bardot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23425</th>\n",
       "      <td>The L-Shaped Room</td>\n",
       "      <td>Leslie Caron</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23724</th>\n",
       "      <td>Barabbas</td>\n",
       "      <td>Anthony Quinn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24722</th>\n",
       "      <td>Boccaccio '70</td>\n",
       "      <td>Anita Ekberg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25499</th>\n",
       "      <td>Bon Voyage!</td>\n",
       "      <td>Fred MacMurray</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30923</th>\n",
       "      <td>The Intruder</td>\n",
       "      <td>William Shatner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30982</th>\n",
       "      <td>Days of Wine and Roses</td>\n",
       "      <td>Jack Lemmon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31048</th>\n",
       "      <td>Mr. Hobbs Takes a Vacation</td>\n",
       "      <td>James Stewart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31065</th>\n",
       "      <td>The Man Who Shot Liberty Valance</td>\n",
       "      <td>James Stewart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33430</th>\n",
       "      <td>Billy Budd</td>\n",
       "      <td>Terence Stamp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36044</th>\n",
       "      <td>The Counterfeit Traitor</td>\n",
       "      <td>William Holden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43079</th>\n",
       "      <td>State Fair</td>\n",
       "      <td>Pat Boone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43201</th>\n",
       "      <td>Cape Fear</td>\n",
       "      <td>Gregory Peck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43229</th>\n",
       "      <td>To Kill a Mockingbird</td>\n",
       "      <td>Gregory Peck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43864</th>\n",
       "      <td>Big and Little Wong Tin Bar</td>\n",
       "      <td>Jackie Chan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45752</th>\n",
       "      <td>Atraco a las tres</td>\n",
       "      <td>JosÃ© Luis LÃ³pez VÃ¡zquez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45973</th>\n",
       "      <td>Gypsy</td>\n",
       "      <td>Rosalind Russell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48611</th>\n",
       "      <td>Lycanthropus</td>\n",
       "      <td>Barbara Lass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49563</th>\n",
       "      <td>Ride the High Country</td>\n",
       "      <td>Joel McCrea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50111</th>\n",
       "      <td>Sundays and Cybele</td>\n",
       "      <td>Hardy KrÃ¼ger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50726</th>\n",
       "      <td>Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)</td>\n",
       "      <td>Aleksandr Shvorin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50825</th>\n",
       "      <td>Gorath</td>\n",
       "      <td>Ryo Ikebe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51496</th>\n",
       "      <td>The Music Man</td>\n",
       "      <td>Robert Preston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52509</th>\n",
       "      <td>Mamma Roma</td>\n",
       "      <td>Anna Magnani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54687</th>\n",
       "      <td>Billy Rose's Jumbo</td>\n",
       "      <td>Doris Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54702</th>\n",
       "      <td>That Touch of Mink</td>\n",
       "      <td>Doris Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55794</th>\n",
       "      <td>The 300 Spartans</td>\n",
       "      <td>Richard Egan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57225</th>\n",
       "      <td>Kid Galahad</td>\n",
       "      <td>Elvis Presley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57619</th>\n",
       "      <td>La commare secca</td>\n",
       "      <td>Marisa Solinas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64478</th>\n",
       "      <td>Carnival of Souls</td>\n",
       "      <td>Candace Hilligoss</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64701</th>\n",
       "      <td>Carry On Cruising</td>\n",
       "      <td>Sid James</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66315</th>\n",
       "      <td>Half Ticket</td>\n",
       "      <td>Kishore Kumar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67489</th>\n",
       "      <td>Eyes Without a Face</td>\n",
       "      <td>Pierre Brasseur</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71444</th>\n",
       "      <td>Through a Glass Darkly</td>\n",
       "      <td>Harriet Andersson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71544</th>\n",
       "      <td>The Trial</td>\n",
       "      <td>Anthony Perkins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75438</th>\n",
       "      <td>Professor</td>\n",
       "      <td>Shammi Kapoor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79898</th>\n",
       "      <td>Salvatore Giuliano</td>\n",
       "      <td>Salvo Randone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81684</th>\n",
       "      <td>On the Beat</td>\n",
       "      <td>Norman Wisdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83278</th>\n",
       "      <td>Harakiri</td>\n",
       "      <td>Tatsuya Nakadai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84132</th>\n",
       "      <td>Gigot</td>\n",
       "      <td>Jackie Gleason</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86296</th>\n",
       "      <td>Phaedra</td>\n",
       "      <td>Melina Mercouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86491</th>\n",
       "      <td>Village of Daughters</td>\n",
       "      <td>Eric Sykes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88121</th>\n",
       "      <td>Jigsaw</td>\n",
       "      <td>Jack Warner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91267</th>\n",
       "      <td>Pitfall</td>\n",
       "      <td>Hisashi Igawa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91561</th>\n",
       "      <td>The Loneliness of the Long Distance Runner</td>\n",
       "      <td>Tom Courtenay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91797</th>\n",
       "      <td>Mother Joan of the Angels</td>\n",
       "      <td>Lucyna Winnicka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93193</th>\n",
       "      <td>Life for Ruth</td>\n",
       "      <td>Michael Craig</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98387</th>\n",
       "      <td>Os Cafajestes</td>\n",
       "      <td>Daniel Filho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99740</th>\n",
       "      <td>Prison</td>\n",
       "      <td>Doris Svedlund</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107004</th>\n",
       "      <td>The Exterminating Angel</td>\n",
       "      <td>Silvia Pinal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107256</th>\n",
       "      <td>The Four Days of Naples</td>\n",
       "      <td>Regina Bianchi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108865</th>\n",
       "      <td>The Longest Day</td>\n",
       "      <td>John Wayne&lt;br /&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111945</th>\n",
       "      <td>Two Half Times in Hell</td>\n",
       "      <td>Imre Sinkovits</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112366</th>\n",
       "      <td>Varan the Unbelievable</td>\n",
       "      <td>KÃ´zÃ´ Nomura</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              title  \\\n",
       "3454                            Birdman of Alcatraz   \n",
       "4041               What Ever Happened to Baby Jane?   \n",
       "4099                                 David and Lisa   \n",
       "5668                           Experiment in Terror   \n",
       "6323                          Who's Got the Action?   \n",
       "6334                                It's Only Money   \n",
       "7302                                  Term of Trial   \n",
       "8093                                Boys' Night Out   \n",
       "8696                                       La notte   \n",
       "10637                 Long Day's Journey into Night   \n",
       "11199                                   Gay Purr-ee   \n",
       "12688                          Mutiny on the Bounty   \n",
       "13107                              A Kind of Loving   \n",
       "13173                                  Tutti a casa   \n",
       "13668                          Period of Adjustment   \n",
       "15216                           An Autumn Afternoon   \n",
       "15244                                   Sergeants 3   \n",
       "15254                      The Manchurian Candidate   \n",
       "15421                                     L'Eclisse   \n",
       "15674                                Love at Twenty   \n",
       "16583                           Panic in Year Zero!   \n",
       "16594                          The Premature Burial   \n",
       "18690                    Sweet Bird of Youth (film)   \n",
       "19133                                        Dr. No   \n",
       "19511                                Pressure Point   \n",
       "20641                               Tower of London   \n",
       "20958                      The Phantom of the Opera   \n",
       "22120                  A Symposium on Popular Songs   \n",
       "23204                    In Search of the Castaways   \n",
       "23392           A Very Private Affair (Vie PrivÃ©e)   \n",
       "23425                             The L-Shaped Room   \n",
       "23724                                      Barabbas   \n",
       "24722                                 Boccaccio '70   \n",
       "25499                                   Bon Voyage!   \n",
       "30923                                  The Intruder   \n",
       "30982                        Days of Wine and Roses   \n",
       "31048                    Mr. Hobbs Takes a Vacation   \n",
       "31065              The Man Who Shot Liberty Valance   \n",
       "33430                                    Billy Budd   \n",
       "36044                       The Counterfeit Traitor   \n",
       "43079                                    State Fair   \n",
       "43201                                     Cape Fear   \n",
       "43229                         To Kill a Mockingbird   \n",
       "43864                   Big and Little Wong Tin Bar   \n",
       "45752                             Atraco a las tres   \n",
       "45973                                         Gypsy   \n",
       "48611                                  Lycanthropus   \n",
       "49563                         Ride the High Country   \n",
       "50111                            Sundays and Cybele   \n",
       "50726   Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)   \n",
       "50825                                        Gorath   \n",
       "51496                                 The Music Man   \n",
       "52509                                    Mamma Roma   \n",
       "54687                            Billy Rose's Jumbo   \n",
       "54702                            That Touch of Mink   \n",
       "55794                              The 300 Spartans   \n",
       "57225                                   Kid Galahad   \n",
       "57619                              La commare secca   \n",
       "64478                             Carnival of Souls   \n",
       "64701                             Carry On Cruising   \n",
       "66315                                   Half Ticket   \n",
       "67489                           Eyes Without a Face   \n",
       "71444                        Through a Glass Darkly   \n",
       "71544                                     The Trial   \n",
       "75438                                     Professor   \n",
       "79898                            Salvatore Giuliano   \n",
       "81684                                   On the Beat   \n",
       "83278                                      Harakiri   \n",
       "84132                                         Gigot   \n",
       "86296                                       Phaedra   \n",
       "86491                          Village of Daughters   \n",
       "88121                                        Jigsaw   \n",
       "91267                                       Pitfall   \n",
       "91561    The Loneliness of the Long Distance Runner   \n",
       "91797                     Mother Joan of the Angels   \n",
       "93193                                 Life for Ruth   \n",
       "98387                                 Os Cafajestes   \n",
       "99740                                        Prison   \n",
       "107004                      The Exterminating Angel   \n",
       "107256                      The Four Days of Naples   \n",
       "108865                              The Longest Day   \n",
       "111945                       Two Half Times in Hell   \n",
       "112366                       Varan the Unbelievable   \n",
       "\n",
       "                              name  \n",
       "3454                Burt Lancaster  \n",
       "4041                   Bette Davis  \n",
       "4099                   Keir Dullea  \n",
       "5668                    Glenn Ford  \n",
       "6323                   Dean Martin  \n",
       "6334                   Jerry Lewis  \n",
       "7302              Laurence Olivier  \n",
       "8093                     Kim Novak  \n",
       "8696          Marcello Mastroianni  \n",
       "10637            Katharine Hepburn  \n",
       "11199                 Judy Garland  \n",
       "12688                Marlon Brando  \n",
       "13107                   Alan Bates  \n",
       "13173                Alberto Sordi  \n",
       "13668            Anthony Franciosa  \n",
       "15216                   Chishu Ryu  \n",
       "15244                Frank Sinatra  \n",
       "15254                Frank Sinatra  \n",
       "15421                  Alain Delon  \n",
       "15674           Jean-Pierre LÃ©aud  \n",
       "16583                  Ray Milland  \n",
       "16594                  Ray Milland  \n",
       "18690                  Paul Newman  \n",
       "19133                 Sean Connery  \n",
       "19511               Sidney Poitier  \n",
       "20641                Vincent Price  \n",
       "20958                  Herbert Lom  \n",
       "22120                   Paul Frees  \n",
       "23204                 Hayley Mills  \n",
       "23392              Brigitte Bardot  \n",
       "23425                 Leslie Caron  \n",
       "23724                Anthony Quinn  \n",
       "24722                 Anita Ekberg  \n",
       "25499               Fred MacMurray  \n",
       "30923              William Shatner  \n",
       "30982                  Jack Lemmon  \n",
       "31048                James Stewart  \n",
       "31065                James Stewart  \n",
       "33430                Terence Stamp  \n",
       "36044               William Holden  \n",
       "43079                    Pat Boone  \n",
       "43201                 Gregory Peck  \n",
       "43229                 Gregory Peck  \n",
       "43864                  Jackie Chan  \n",
       "45752   JosÃ© Luis LÃ³pez VÃ¡zquez  \n",
       "45973             Rosalind Russell  \n",
       "48611                 Barbara Lass  \n",
       "49563                  Joel McCrea  \n",
       "50111                Hardy KrÃ¼ger  \n",
       "50726            Aleksandr Shvorin  \n",
       "50825                    Ryo Ikebe  \n",
       "51496               Robert Preston  \n",
       "52509                 Anna Magnani  \n",
       "54687                    Doris Day  \n",
       "54702                    Doris Day  \n",
       "55794                 Richard Egan  \n",
       "57225                Elvis Presley  \n",
       "57619               Marisa Solinas  \n",
       "64478            Candace Hilligoss  \n",
       "64701                    Sid James  \n",
       "66315                Kishore Kumar  \n",
       "67489              Pierre Brasseur  \n",
       "71444            Harriet Andersson  \n",
       "71544              Anthony Perkins  \n",
       "75438                Shammi Kapoor  \n",
       "79898                Salvo Randone  \n",
       "81684                Norman Wisdom  \n",
       "83278              Tatsuya Nakadai  \n",
       "84132               Jackie Gleason  \n",
       "86296              Melina Mercouri  \n",
       "86491                   Eric Sykes  \n",
       "88121                  Jack Warner  \n",
       "91267                Hisashi Igawa  \n",
       "91561                Tom Courtenay  \n",
       "91797              Lucyna Winnicka  \n",
       "93193                Michael Craig  \n",
       "98387                 Daniel Filho  \n",
       "99740               Doris Svedlund  \n",
       "107004                Silvia Pinal  \n",
       "107256              Regina Bianchi  \n",
       "108865            John Wayne<br />  \n",
       "111945              Imre Sinkovits  \n",
       "112366               KÃ´zÃ´ Nomura  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #7\n",
    "a.loc[(a['yr']==1962) & (a['ord']==1), ['title', 'name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Busy years for Rock Hudson\n",
    "\n",
    "Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1953</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1961</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr  n\n",
       "2  1953  5\n",
       "8  1961  3"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #7\n",
    "b = (a.loc[a['name']=='Rock Hudson', ['yr', 'title']]\n",
    "     .groupby('yr').count()\n",
    "     .reset_index()\n",
    "     .rename(columns={'title': 'n'}))\n",
    "b.loc[b['n']>2, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Lead actor in Julie Andrews movies\n",
    "\n",
    "List the film title and the leading actor for all of the films 'Julie Andrews' played in.\n",
    "\n",
    "> _Did you get \"Little Miss Marker twice\"?_   \n",
    "> Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).\n",
    ">\n",
    "> Title is not a unique field, create a table of IDs in your subquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1201</th>\n",
       "      <td>10</td>\n",
       "      <td>Dudley Moore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1212</th>\n",
       "      <td>Darling Lili</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1214</th>\n",
       "      <td>Duet for One</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1215</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1217</th>\n",
       "      <td>Mary Poppins</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1218</th>\n",
       "      <td>Relative Values</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1220</th>\n",
       "      <td>Star!</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1226</th>\n",
       "      <td>The Tamarind Seed</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1227</th>\n",
       "      <td>Thoroughly Modern Millie</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1230</th>\n",
       "      <td>Victor Victoria</td>\n",
       "      <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7715</th>\n",
       "      <td>Shrek the Third</td>\n",
       "      <td>Mike Myers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13694</th>\n",
       "      <td>Little Miss Marker</td>\n",
       "      <td>Walter Matthau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18702</th>\n",
       "      <td>Torn Curtain</td>\n",
       "      <td>Paul Newman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20884</th>\n",
       "      <td>The Pink Panther Strikes Again</td>\n",
       "      <td>Peter Sellers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33127</th>\n",
       "      <td>The Princess Diaries</td>\n",
       "      <td>Anne Hathaway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33128</th>\n",
       "      <td>The Princess Diaries 2: Royal Engagement</td>\n",
       "      <td>Anne Hathaway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39450</th>\n",
       "      <td>Despicable Me</td>\n",
       "      <td>Steve Carell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45551</th>\n",
       "      <td>The Americanization of Emily</td>\n",
       "      <td>James Garner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50999</th>\n",
       "      <td>Tooth Fairy</td>\n",
       "      <td>Dwayne Johnson</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          title            name\n",
       "1201                                         10    Dudley Moore\n",
       "1212                               Darling Lili   Julie Andrews\n",
       "1214                               Duet for One   Julie Andrews\n",
       "1215                                     Hawaii   Julie Andrews\n",
       "1217                               Mary Poppins   Julie Andrews\n",
       "1218                            Relative Values   Julie Andrews\n",
       "1220                                      Star!   Julie Andrews\n",
       "1226                          The Tamarind Seed   Julie Andrews\n",
       "1227                   Thoroughly Modern Millie   Julie Andrews\n",
       "1230                            Victor Victoria   Julie Andrews\n",
       "7715                            Shrek the Third      Mike Myers\n",
       "13694                        Little Miss Marker  Walter Matthau\n",
       "18702                              Torn Curtain     Paul Newman\n",
       "20884            The Pink Panther Strikes Again   Peter Sellers\n",
       "33127                      The Princess Diaries   Anne Hathaway\n",
       "33128  The Princess Diaries 2: Royal Engagement   Anne Hathaway\n",
       "39450                             Despicable Me    Steve Carell\n",
       "45551              The Americanization of Emily    James Garner\n",
       "50999                               Tooth Fairy  Dwayne Johnson"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #7\n",
    "b = a.loc[a['name']=='Julie Andrews', 'movieid'].values\n",
    "a.loc[(a['movieid'].isin(b)) & (a['ord']==1), ['title', 'name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. Actors with 15 leading roles\n",
    "\n",
    "Obtain a list, in alphabetical order, of actors who've had at least 15 **starring** roles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>264</th>\n",
       "      <td>Adam Sandler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>Al Pacino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>Anthony Hopkins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1810</th>\n",
       "      <td>Antonio Banderas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1611</th>\n",
       "      <td>Arnold Schwarzenegger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>623</th>\n",
       "      <td>Barbara Stanwyck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1601</th>\n",
       "      <td>Ben Affleck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Bette Davis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1549</th>\n",
       "      <td>Bing Crosby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Bruce Willis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918</th>\n",
       "      <td>Bud Abbott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Burt Lancaster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1659</th>\n",
       "      <td>Burt Reynolds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>512</th>\n",
       "      <td>Buster Keaton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1185</th>\n",
       "      <td>Cary Grant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Charles Bronson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>Charlton Heston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>Clark Gable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1595</th>\n",
       "      <td>Claudette Colbert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>Clint Eastwood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>Dean Martin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1237</th>\n",
       "      <td>Dennis Quaid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>Denzel Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1075</th>\n",
       "      <td>Dustin Hoffman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>553</th>\n",
       "      <td>Edward G. Robinson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2157</th>\n",
       "      <td>Elvis Presley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1032</th>\n",
       "      <td>Errol Flynn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>Frank Sinatra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>Fred Astaire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>803</th>\n",
       "      <td>Fredric March</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492</th>\n",
       "      <td>Gary Cooper</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>958</th>\n",
       "      <td>Gene Hackman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1864</th>\n",
       "      <td>George Clooney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>Glenn Ford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1582</th>\n",
       "      <td>Gregory Peck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>900</th>\n",
       "      <td>Greta Garbo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>Harrison Ford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Henry Fonda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>985</th>\n",
       "      <td>Humphrey Bogart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>878</th>\n",
       "      <td>Ingrid Bergman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1114</th>\n",
       "      <td>Jack Lemmon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>765</th>\n",
       "      <td>Jack Nicholson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1605</th>\n",
       "      <td>Jackie Chan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>James Cagney</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>James Mason</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1116</th>\n",
       "      <td>James Stewart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>Jane Fonda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2071</th>\n",
       "      <td>Jean-Claude Van Damme</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>Jeff Bridges</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>Jerry Lewis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>405</th>\n",
       "      <td>Jim Carrey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>892</th>\n",
       "      <td>Joan Crawford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>372</th>\n",
       "      <td>John Barrymore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>420</th>\n",
       "      <td>John Travolta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>John Wayne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>705</th>\n",
       "      <td>Johnny Depp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>875</th>\n",
       "      <td>Keanu Reeves</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>729</th>\n",
       "      <td>Kevin Costner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Kirk Douglas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1776</th>\n",
       "      <td>Kurt Russell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>606</th>\n",
       "      <td>Lana Turner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>Laurence Olivier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>571</th>\n",
       "      <td>Leonardo DiCaprio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>443</th>\n",
       "      <td>Marlon Brando</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1315</th>\n",
       "      <td>Mary Pickford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1261</th>\n",
       "      <td>Matt Damon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>582</th>\n",
       "      <td>Matt Dillon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1085</th>\n",
       "      <td>Mel Gibson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>Meryl Streep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1150</th>\n",
       "      <td>Michael Caine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>Michael Douglas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1868</th>\n",
       "      <td>Michael Keaton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>Nicolas Cage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>691</th>\n",
       "      <td>Paul Newman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>776</th>\n",
       "      <td>Peter Sellers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2187</th>\n",
       "      <td>Randolph Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>Robert De Niro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>286</th>\n",
       "      <td>Robert Mitchum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>940</th>\n",
       "      <td>Robert Taylor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>Robin Williams</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>Rock Hudson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>Ronald Colman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>702</th>\n",
       "      <td>Sean Connery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2284</th>\n",
       "      <td>See below</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1168</th>\n",
       "      <td>Shirley Temple</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Spencer Tracy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>Stan Laurel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>787</th>\n",
       "      <td>Steve Martin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1540</th>\n",
       "      <td>Sylvester Stallone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1661</th>\n",
       "      <td>Tom Berenger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1265</th>\n",
       "      <td>Tom Cruise</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>596</th>\n",
       "      <td>Tom Hanks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>737</th>\n",
       "      <td>Tommy Lee Jones</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908</th>\n",
       "      <td>Tyrone Power</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>487</th>\n",
       "      <td>Walter Matthau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>William Garwood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1303</th>\n",
       "      <td>William Holden</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       name\n",
       "264            Adam Sandler\n",
       "304               Al Pacino\n",
       "303         Anthony Hopkins\n",
       "1810       Antonio Banderas\n",
       "1611  Arnold Schwarzenegger\n",
       "623        Barbara Stanwyck\n",
       "1601            Ben Affleck\n",
       "141             Bette Davis\n",
       "1549            Bing Crosby\n",
       "73             Bruce Willis\n",
       "918              Bud Abbott\n",
       "112          Burt Lancaster\n",
       "1659          Burt Reynolds\n",
       "512           Buster Keaton\n",
       "1185             Cary Grant\n",
       "56          Charles Bronson\n",
       "280         Charlton Heston\n",
       "509             Clark Gable\n",
       "1595      Claudette Colbert\n",
       "501          Clint Eastwood\n",
       "231             Dean Martin\n",
       "1237           Dennis Quaid\n",
       "796       Denzel Washington\n",
       "1075         Dustin Hoffman\n",
       "553      Edward G. Robinson\n",
       "2157          Elvis Presley\n",
       "1032            Errol Flynn\n",
       "552           Frank Sinatra\n",
       "445            Fred Astaire\n",
       "803           Fredric March\n",
       "492             Gary Cooper\n",
       "958            Gene Hackman\n",
       "1864         George Clooney\n",
       "213              Glenn Ford\n",
       "1582           Gregory Peck\n",
       "900             Greta Garbo\n",
       "559           Harrison Ford\n",
       "70              Henry Fonda\n",
       "985         Humphrey Bogart\n",
       "878          Ingrid Bergman\n",
       "1114            Jack Lemmon\n",
       "765          Jack Nicholson\n",
       "1605            Jackie Chan\n",
       "86             James Cagney\n",
       "137             James Mason\n",
       "1116          James Stewart\n",
       "341              Jane Fonda\n",
       "2071  Jean-Claude Van Damme\n",
       "318            Jeff Bridges\n",
       "232             Jerry Lewis\n",
       "405              Jim Carrey\n",
       "892           Joan Crawford\n",
       "372          John Barrymore\n",
       "420           John Travolta\n",
       "224              John Wayne\n",
       "705             Johnny Depp\n",
       "875            Keanu Reeves\n",
       "729           Kevin Costner\n",
       "136            Kirk Douglas\n",
       "1776           Kurt Russell\n",
       "606             Lana Turner\n",
       "258        Laurence Olivier\n",
       "571       Leonardo DiCaprio\n",
       "443           Marlon Brando\n",
       "1315          Mary Pickford\n",
       "1261             Matt Damon\n",
       "582             Matt Dillon\n",
       "1085             Mel Gibson\n",
       "351            Meryl Streep\n",
       "1150          Michael Caine\n",
       "397         Michael Douglas\n",
       "1868         Michael Keaton\n",
       "330            Nicolas Cage\n",
       "691             Paul Newman\n",
       "776           Peter Sellers\n",
       "2187         Randolph Scott\n",
       "96           Robert De Niro\n",
       "286          Robert Mitchum\n",
       "940           Robert Taylor\n",
       "350          Robin Williams\n",
       "494             Rock Hudson\n",
       "472           Ronald Colman\n",
       "702            Sean Connery\n",
       "2284              See below\n",
       "1168         Shirley Temple\n",
       "140           Spencer Tracy\n",
       "344             Stan Laurel\n",
       "787            Steve Martin\n",
       "1540     Sylvester Stallone\n",
       "1661           Tom Berenger\n",
       "1265             Tom Cruise\n",
       "596               Tom Hanks\n",
       "737         Tommy Lee Jones\n",
       "908            Tyrone Power\n",
       "487          Walter Matthau\n",
       "685         William Garwood\n",
       "1303         William Holden"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #7\n",
    "b = (a.loc[a['ord']==1, ['name', 'actorid', 'movieid']]\n",
    "     .groupby(['actorid', 'name'])\n",
    "     .count()\n",
    "     .reset_index()\n",
    "     .rename(columns={'movieid': 'n'}))\n",
    "b.loc[b['n']>=15, ['name']].sort_values('name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "List the films released in the year 1978 ordered by the number of actors in the cast, then by title."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>The Bad News Bears Go to Japan</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>The Swarm</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Grease</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>American Hot Wax</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>The Boys from Brazil</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Lies My Father Told Me</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Same Time, Next Year</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Somebody Killed Her Husband</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>That's Carry On!</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>The 36th Chamber of Shaolin</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>104 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             title   n\n",
       "75  The Bad News Bears Go to Japan  50\n",
       "94                       The Swarm  37\n",
       "36                          Grease  28\n",
       "5                 American Hot Wax  27\n",
       "77            The Boys from Brazil  26\n",
       "..                             ...  ..\n",
       "52          Lies My Father Told Me   2\n",
       "66            Same Time, Next Year   2\n",
       "70     Somebody Killed Her Husband   2\n",
       "72                That's Carry On!   2\n",
       "73     The 36th Chamber of Shaolin   2\n",
       "\n",
       "[104 rows x 2 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #7\n",
    "b = (a.loc[a['yr']==1978, ['title', 'movieid', 'actorid']]\n",
    "      .groupby(['title', 'movieid'])\n",
    "      .count()\n",
    "      .reset_index()\n",
    "      .rename(columns={'actorid': 'n'})\n",
    "      .loc[:, ['title', 'n']])\n",
    "b.sort_values(['n', 'title'], ascending=[False, True])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "\n",
    "List all the people who have worked with 'Art Garfunkel'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>980</th>\n",
       "      <td>Beverly Johnson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14279</th>\n",
       "      <td>Bill Paxton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>959</th>\n",
       "      <td>Breckin Meyer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>981</th>\n",
       "      <td>Bruce Jay Friedman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>975</th>\n",
       "      <td>Cecilie Thomsen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972</th>\n",
       "      <td>Cindy Crawford</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974</th>\n",
       "      <td>Donald Trump</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>969</th>\n",
       "      <td>Elio Fiorucci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>966</th>\n",
       "      <td>Ellen Albertini Dow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>976</th>\n",
       "      <td>Frederique van der Wal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>971</th>\n",
       "      <td>Georgina Grenville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36093</th>\n",
       "      <td>Harris Yulin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>962</th>\n",
       "      <td>Heather Matarazzo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>973</th>\n",
       "      <td>Heidi Klum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14278</th>\n",
       "      <td>Julian Sands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14281</th>\n",
       "      <td>Kurtwood Smith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>964</th>\n",
       "      <td>Lauren Hutton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>982</th>\n",
       "      <td>Lorna Luft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>953</th>\n",
       "      <td>Mark Ruffalo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>965</th>\n",
       "      <td>Michael York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>955</th>\n",
       "      <td>Mike Myers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>956</th>\n",
       "      <td>Neve Campbell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>979</th>\n",
       "      <td>Peter Bogdanovich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36094</th>\n",
       "      <td>Robert DoQui</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>968</th>\n",
       "      <td>Ron Jeremy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>954</th>\n",
       "      <td>Ryan Phillippe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>957</th>\n",
       "      <td>Salma Hayek</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>958</th>\n",
       "      <td>Sela Ward</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14280</th>\n",
       "      <td>Sherilyn Fenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>960</th>\n",
       "      <td>Sherry Stringfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>970</th>\n",
       "      <td>Sheryl Crow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>963</th>\n",
       "      <td>Skipp Sudduth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>984</th>\n",
       "      <td>Stars on 54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>967</th>\n",
       "      <td>Thelma Houston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>983</th>\n",
       "      <td>Valerie Perrine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>977</th>\n",
       "      <td>Veronica Webb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>961</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         name\n",
       "980           Beverly Johnson\n",
       "14279             Bill Paxton\n",
       "959             Breckin Meyer\n",
       "981        Bruce Jay Friedman\n",
       "975           Cecilie Thomsen\n",
       "972            Cindy Crawford\n",
       "974              Donald Trump\n",
       "969             Elio Fiorucci\n",
       "966       Ellen Albertini Dow\n",
       "976    Frederique van der Wal\n",
       "971        Georgina Grenville\n",
       "36093            Harris Yulin\n",
       "962         Heather Matarazzo\n",
       "973                Heidi Klum\n",
       "14278            Julian Sands\n",
       "14281          Kurtwood Smith\n",
       "964             Lauren Hutton\n",
       "982                Lorna Luft\n",
       "953              Mark Ruffalo\n",
       "965              Michael York\n",
       "955                Mike Myers\n",
       "956             Neve Campbell\n",
       "979         Peter Bogdanovich\n",
       "36094            Robert DoQui\n",
       "968                Ron Jeremy\n",
       "954            Ryan Phillippe\n",
       "957               Salma Hayek\n",
       "958                 Sela Ward\n",
       "14280           Sherilyn Fenn\n",
       "960        Sherry Stringfield\n",
       "970               Sheryl Crow\n",
       "963             Skipp Sudduth\n",
       "984               Stars on 54\n",
       "967            Thelma Houston\n",
       "983           Valerie Perrine\n",
       "977             Veronica Webb\n",
       "961                       NaN"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a was obtained in #7\n",
    "b = a.loc[a['name']=='Art Garfunkel', 'movieid']\n",
    "a.loc[(a['movieid'].isin(b)) & (a['name']!='Art Garfunkel'),\n",
    "      ['name']].sort_values('name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
